﻿from sqlalchemy import (
    Column,
    Table,
    Integer,
    Text,
    Unicode,
    Boolean,
    DateTime,
    ForeignKey,
    Float,
    desc,
    )


from sqlalchemy.orm import (
    relationship,
    backref,
    )

from sqlalchemy.ext.hybrid import hybrid_property

from . import tz
from .meta import Base
from ..freedom.selectlist import getoptions_singletext
import datetime


class CouponRandom(Base):
    __tablename__ = 'couponRandom'
    id = Column(Integer, primary_key=True)
    randomserial = Column(Unicode(20), default=u'')
    couponid = Column(Integer, ForeignKey('coupon.id')) 
    userid = Column(Integer, ForeignKey('user.id')) 
    createdate = Column(DateTime, default = datetime.datetime.now(tz))
    useddate = Column(DateTime)
    isused = Column(Boolean, default=False)

    @hybrid_property
    def statustext(self):
        if self.isused:
            return u'已使用'
        if self.coupon.enddate < datetime.datetime.now(tz).replace(tzinfo=None):
            return u'已过期'
        return u'有效'



class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    email = Column(Unicode(50), default=u'')
    password = Column(Unicode(50), default=u'')
    name = Column(Unicode(50), default=u'')
    realname = Column(Unicode(50), default=u'')
    gender = Column(Integer, default=-1)
    subject = Column(Unicode(50), default=u'')
    schoolnumber = Column(Unicode(20), default=u'')
    tel = Column(Unicode(20), default=u'')
    address = Column(Unicode(255), default=u'')
    iscreatedbyadmin = Column(Boolean, default=False)
    couponrandoms = relationship("CouponRandom", backref="user", order_by=CouponRandom.createdate)
    checked = Column(Boolean, default=False)
    enable = Column(Boolean, default=True)
    createdate = Column(DateTime, default = datetime.datetime.now(tz))
    roleid = Column(Integer, ForeignKey('role.id'))
    groupid = Column(Integer, ForeignKey('group.id')) 

class Role(Base):
    __tablename__ = 'role'
    id = Column(Integer, primary_key=True)
    name = Column(Unicode(100), default=u'', unique=True)
    description = Column(Text, default=u'')
    users = relationship("User", backref="role") 

class Group(Base):
    __tablename__ = 'group'
    id = Column(Integer, primary_key=True)
    name = Column(Unicode(100), default=u'')
    description = Column(Text, default=u'')
    groupid = Column(Integer, ForeignKey('group.id'))
    fathergroup = relationship('Group', remote_side = id, backref = backref('songroups'))
    users = relationship("User", backref="group") 


class BusinessUser(Base):
    __tablename__ = 'businessUser'
    id = Column(Integer, primary_key=True)
    login = Column(Unicode(50), default=u'')
    password = Column(Unicode(50), default=u'')
    companyname = Column(Unicode(50), default=u'')
    businesstype = Column(Integer, default=-1)
    email = Column(Unicode(50), default=u'')
    contactname = Column(Unicode(10), default=u'')
    contacttel = Column(Unicode(20), default=u'')
    qq = Column(Unicode(20), default=u'')
    othercontacttool = Column(Unicode(50), default=u'')
    address = Column(Unicode(100), default=u'')
    introduce = Column(Text, default=u'')
    special = Column(Unicode(50), default=u'')
    picture = Column(Unicode(255), default=u'')
    score = Column(Integer, default=0)
    checked = Column(Boolean, default=False)
    enable = Column(Boolean, default=False)
    createdate = Column(DateTime, default = datetime.datetime.now(tz))
    coupons = relationship("Coupon", backref="businessuser") 

    
class Coupon(Base):
    __tablename__ = 'coupon'
    id = Column(Integer, primary_key=True)
    name = Column(Unicode(20), default=u'')
    coupontype = Column(Integer, default=-1)
    shortdetail = Column(Text, default=u'')
    detail = Column(Text, default=u'')
    number = Column(Integer)
    rule = Column(Unicode(10), default=u'')
    startdate = Column(DateTime)
    enddate = Column(DateTime)
    picture = Column(Unicode(255), default=u'')
    status = Column(Integer)
    createdate = Column(DateTime, default = datetime.datetime.now(tz))
    businessUser_id = Column(Integer, ForeignKey('businessUser.id')) 
    couponrandoms = relationship("CouponRandom", backref="coupon") 
    deleted = Column(Boolean, default=False)

    @hybrid_property
    def leftdays(self):
        import datetime
        return max((self.enddate - datetime.datetime.now(tz).replace(tzinfo=None)).days, 0)

    @hybrid_property
    def coupontypetext(self):
        return getoptions_singletext('coupontype', self.coupontype)

    @hybrid_property
    def statustext(self):
        return getoptions_singletext('couponstatus', self.status)

    def __json__(self, request):
        return {
                'id':self.id,
                'name':self.name,
                'coupontype': getoptions_singletext('coupontype', self.coupontype),
                'number':self.number,
                'rule':self.rule,
                'startdate':self.startdate.strftime('%Y-%m-%d'),
                'enddate':self.enddate.strftime('%Y-%m-%d'),
                'picture':self.picture,
                'status':getoptions_singletext('couponstatus', self.status),
                'createdate':self.createdate.strftime('%Y-%m-%d %H:%M:%S')
               }

class CouponComment(Base):
    __tablename__ = 'couponComment'
    id = Column(Integer, primary_key=True)
    score = Column(Integer, default=-1)
    detail = Column(Text, default=u'')
    userid = Column(Integer, ForeignKey('user.id')) 
    couponrandomid = Column(Integer, ForeignKey('couponRandom.id')) 

class ProductCategory(Base):
    __tablename__ = 'productCategory'
    id = Column(Integer, primary_key=True)
    name = Column(Unicode(20), default=u'')
    priority = Column(Float, default=0.00)
    category_id = Column(Integer, ForeignKey('productCategory.id'))
    deleted = Column(Boolean, default=False)
    fatherCategory = relationship('ProductCategory', remote_side = id, backref = backref('sonCategories'))
    products = relationship("Product", backref='category')

class Product(Base):
    __tablename__ = 'product'
    id = Column(Integer, primary_key=True)
    name = Column(Unicode(200), default=u'')
    picture = Column(Unicode(255), default=u'')
    shortdetail = Column(Text, default=u'')
    detail = Column(Text, default=u'')
    marketprice = Column(Float, default=0.00)
    sellprice = Column(Float, default=0.00)
    recommended = Column(Boolean, default=False)
    enable = Column(Boolean, default=False)
    createdate = Column(DateTime, default = datetime.datetime.now(tz))
    deleted = Column(Boolean, default=False)
    category_id = Column(Integer, ForeignKey('productCategory.id')) 

class ProductOrder(Base):
    __tablename__ = 'productOrder'
    id = Column(Integer, primary_key=True)
    orderSN = Column(Unicode(30), default=u'')
    deliverAddress = Column(Unicode(255), default=u'')
    receiverName = Column(Unicode(10), default=u'')
    contactTel = Column(Unicode(20), default=u'')
    comment = Column(Unicode(255), default=u'')
    orderStatus = Column(Integer)
    createdate = Column(DateTime, default = datetime.datetime.now(tz))
    deliverdate = Column(DateTime)
    user_id = Column(Integer, ForeignKey('user.id')) 
    couponRandom_id = Column(Integer, ForeignKey('couponRandom.id')) 
    couponRandom = relationship("CouponRandom", uselist=False, backref='productOrder')
    productOrderDetails = relationship("ProductOrderDetail", backref='productOrder')

class ProductOrderDetail(Base):
    __tablename__ = 'productOrderDetail'
    id = Column(Integer, primary_key=True)
    product_id = Column(Integer, ForeignKey('product.id')) 
    product = relationship('Product')
    productOrder_id = Column(Integer, ForeignKey('productOrder.id'))
    name = Column(Unicode(200), default=u'')
    picture = Column(Unicode(255), default=u'')
    marketprice = Column(Float, default=0.00)
    sellprice = Column(Float, default=0.00)
    number = Column(Integer)


